﻿function ExportJQGridDataToExcel(tableCtrl, excelFilename) {
    //  Export the data from our jqGrid into a (real !) Excel .xlsx file.
    //
    //  We'll build up a (very large?) tab-separated string containing the data to be exported, then POST them 
    //  off to a .ashx handler, which creates the Excel file.

    var allJQGridData = $(tableCtrl).jqGrid('getGridParam', 'data');

    var jqgridRowIDs = $(tableCtrl).getDataIDs();                // Fetch the RowIDs for this grid
    var headerData = $(tableCtrl).getRowData(jqgridRowIDs[0]);   // Fetch the list of "name" values in our colModel

    //  For each visible column in our jqGrid, fetch it's Name, and it's Header-Text value
    var columnNames = new Array();       //  The "name" values from our jqGrid colModel
    var columnHeaders = new Array();     //  The Header-Text, from the jqGrid "colNames" section
    var inx = 0;
    var allColumnNames = $(tableCtrl).jqGrid('getGridParam', 'colNames');

    //  If our jqGrid has "MultiSelect" set to true, remove the first (checkbox) column, otherwise we'll
    //  create an exception of: "A potentially dangerous Request.Form value was detected from the client."
    var bIsMultiSelect = $(tableCtrl).jqGrid('getGridParam', 'multiselect');
    if (bIsMultiSelect) {
        allColumnNames.splice(0, 1);
    }

    for (var headerValue in headerData) {
        //  If this column ISN'T hidden, and DOES have a column-name, then we'll export its data to Excel.
        var isColumnHidden = $(tableCtrl).jqGrid("getColProp", headerValue).hidden;
        if (!isColumnHidden && headerValue != null) {
            columnNames.push(headerValue);
            columnHeaders.push(allColumnNames[inx]);
        }
        inx++;
    }

    //  We now need to build up a (potentially very long) tab-separated string containing all of the data (and a header row)
    //  which we'll want to export to Excel.

    //  First, let's append the header row...

    var excelData = '';

    for (var k = 0; k < columnNames.length; k++) {

        //excelData += columnHeaders[k] + "\t";
        excelData += "<td>" + getStr(columnHeaders[k]) + "</td>";

    }
    // excelData = "<tr>" + removeLastChar(excelData) + "</tr><tr>";//+ "\r\n";

    excelData = "<tr>" + getStr(excelData) + "</tr><tr>";


    //  ..then each row of data to be exported.
    var cellValue = '';
    for (i = 0; i < allJQGridData.length; i++) {

        var data = allJQGridData[i];

        for (var j = 0; j < columnNames.length; j++) {

            // Fetch one jqGrid cell's data, but make sure it's a string
            cellValue = '' + getStr(data[columnNames[j]]);

            if (cellValue == null) {

                excelData += ""; //"\t";

            }
            else {
                if (cellValue.indexOf("a href") > -1) {
                    //  Some of my cells have a jqGrid cell with a formatter in them, making them hyperlinks.
                    //  We don't want to export the "<a href..> </a>" tags to our Excel file, just the cell's text.
                    cellValue = getStr($(cellValue).text());
                }
                //  Make sure we are able to POST data containing apostrophes in it
                // cellValue = cellValue.replace(/'/g, "&apos;");
                if (!isNaN(cellValue) && cellValue.length > 15) {
                    excelData += "<td>" + getStr(cellValue) + "'</td>";
                } else {
                    excelData += "<td>" + getStr(cellValue) + "</td>";
                }


            }
        }

        excelData = (excelData) + "</tr>";//+ "\r\n";

    }

    //  Now, we need to POST our Excel Data to our .ashx file *and* redirect to the .ashx file.
    // postAndRedirect("/AppHelper/Views/ExportGridToExcel.ashx?filename=" + excelFilename, { excelData: excelData });
    // postAndRedirect("/ExportExcel/ExportExcelByJQGrid?filename=" + excelFilename, { excelData: excelData });
    //$.post("/ExportExcel/ExportExcelByJQGrid", { filename: excelFilename, excelData: excelData }, function () {

    // })
    if (isIE()) {

        var oXL;
        try {
            oXL = new ActiveXObject("Excel.Application"); //创建AX对象excel  
        } catch (e) {
            alert("无法启动Excel!\n\n如果您确信您的电脑中已经安装了Excel，" + "那么请调整IE的安全级别。\n\n具体操作：\n\n" + "工具 → Internet选项 → 安全 → 自定义级别 → 对没有标记为安全的ActiveX进行初始化和脚本运行 → 启用");
            return false;
        }

        //创建AX对象excel  
        var oWB = oXL.Workbooks.Add();
        //获取workbook对象  
        var oSheet = oWB.ActiveSheet;

        var xlsheet = oWB.Worksheets(1);
        for (var k = 0; k < columnNames.length; k++) {

            oSheet.Cells(1, k + 1).value = getStr(columnHeaders[k]);
        }
        //取得表格行数  
        for (i = 0; i < allJQGridData.length; i++) {

            //取得每行的列数  
            for (var j = 0; j < columnNames.length; j++) {

                // Fetch one jqGrid cell's data, but make sure it's a string
                // oSheet.Cells(i + 2, j + 1).NumberFormatLocal = "@";//设置导出为文本
                oSheet.Cells(i + 2, j + 1) = getStr(data[columnNames[j]]);

            }
        }
        oXL.Visible = true;

        //oXL.Application.GetSaveAsFilename("" + excelFilename + ".xls", "Excel Spreadsheets (*.xls), *.xls");
        oWB.SaveAs("C:\\" + excelFilename + ".xlsx");

    } else {
        tableToExcel("<table>" + excelData + "</table>", excelFilename);
    }

}


function removeLastChar(str) {
    //  Remove the last character from a string
    return str.substring(0, str.length - 1);
}

function postAndRedirect(url, postData) {
    //  Redirect to a URL, and POST some data to it.
    //  Taken from:
    //  http://stackoverflow.com/questions/8389646/send-post-data-on-redirect-with-javascript-jquery
    //
    var postFormStr = "<form method='POST' action='" + url + "'>\n";

    for (var key in postData) {
        if (postData.hasOwnProperty(key)) {
            postFormStr += "<input type='hidden' name='" + key + "' value='" + getStr(postData[key]) + "'></input>";
        }
    }

    postFormStr += "</form>";

    var formElement = $(postFormStr);

    $('body').append(formElement);
    $(formElement).submit();
}

var tableToExcel = (function () {
    var uri = 'data:application/vnd.ms-excel;base64,',
        template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><?xml version="1.0" encoding="UTF-8" standalone="yes"?><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
        base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) },
        format = function (s, c) {
            return s.replace(/{(\w+)}/g,
                function (m, p) { return c[p]; })
        }
    return function (table, name) {

        var ctx = { worksheet: name || 'Worksheet', table: table }
        window.location.href = uri + base64(format(template, ctx))

    }
})()

function isIE() { //ie?
    if (!!window.ActiveXObject || "ActiveXObject" in window)
        return true;
    else
        return false;
}

function getStr(str) {

    if (str == null) {
        return "";
    } else {
        return str;
    }

}